﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Web;
using System.Data;
using System.Data.Odbc;
using System.Configuration;

namespace WLMCoreLibrary
{
    
    public class dbConnector
    {
        private OdbcConnection _connector;
        private bool _error;
        private List<RegistroError> _listaErrores;
        private int _dbType;


        public bool Error { get { return _error; } set { _error = value; } }
        public List<RegistroError> ListaErrores { get { return _listaErrores; } set { _listaErrores = value; } }
        public OdbcConnection connector { get { return _connector; } }
        public bool AutoCommit { get; set; }
        /// <summary>
        /// 1:Mysql
        /// 2:SQLServer
        /// </summary>
        public int DBType { get { return _dbType; } set { _dbType = value; } }

        public dbConnector()
        {
            _connector = new OdbcConnection(ConfigurationManager.AppSettings["MiWebSiteConnectionString"]);
            _listaErrores = new List<RegistroError>();
            try
            {
                _dbType = Convert.ToInt32(ConfigurationManager.AppSettings["dataBaseType"]);
            }
            catch(Exception e)
            {
                _dbType=1;
            }
        }

        public dbConnector(string ConnectionString)
        {
            _connector = new OdbcConnection(ConnectionString);
            _listaErrores = new List<RegistroError>();
        }

        public void OpenConnection()
        {

            try
            {
                _connector.Open();
                if (DBType == 1 && !AutoCommit)
                {
                    OdbcCommand comando = new OdbcCommand();
                    comando.CommandText = "set autocommit=0;";
                    comando.Connection = _connector;

                    comando.ExecuteNonQuery();

                    comando.CommandText = "BEGIN;";
                    comando.Connection = _connector;
                    comando.ExecuteNonQuery();
                }
                //HttpContext.Current.Response.Write(".");
            }
            catch (Exception e)
            {
                HttpContext.Current.Response.Write("Ocurrio un error en la aplicacion, intente nuevamente realizar la peticion.<br><br> el error fue el siguiente: <br>" + e.Message);
                
                //Registro del Error
                Error = true;
                ErrorManagement.LogError(e, HttpContext.Current, this,null);
                //HttpContext.Current.Response.End();
            }
            finally
            {

            }
            //HttpContext.Current.Response.Write("[");
        }

        public bool ExecuteCommand(string queryString)
        {
            OdbcCommand comando = new OdbcCommand();
            //HttpContext.Current.Response.Write(queryString);
            try
            {
                
                if (_connector.State == ConnectionState.Closed)
                    OpenConnection();
                comando.Connection = _connector;
                comando.CommandText = queryString;
                comando.ExecuteNonQuery();
                comando.Dispose();
            }
            catch (Exception e)
            {
                HttpContext.Current.Response.Write("Ocurrio un error en la aplicacion, intente nuevamente realizar la peticion.<br><br> el error fue el siguiente: <br>" + e.Message);
                _connector.Close();

                //Registro del Error
                Error = true;
                ErrorManagement.LogError(e, HttpContext.Current, this,comando);

                //HttpContext.Current.Response.End();
            }
            finally
            {

            }
            return true;
        }

        public OdbcDataReader ExecuteReader(string queryString)
        {
            OdbcDataReader reader = null;
            OdbcCommand comando = new OdbcCommand();
            try
            {
                
                if (_connector.State == ConnectionState.Closed)
                    OpenConnection();

                comando.Connection = _connector;
                comando.CommandText = queryString;
                reader = comando.ExecuteReader();

            }
            catch (Exception e)
            {
                HttpContext.Current.Response.Write("Ocurrio un error en la aplicacion, intente nuevamente realizar la peticion.<br><br> el error fue el siguiente: <br>" + e.Message + "<br><br> " + queryString);
                _connector.Close();
                //Registro del Error
                Error = true;
                ErrorManagement.LogError(e, HttpContext.Current, this,comando);

                //HttpContext.Current.Response.End();
            }
            finally
            {

            }
            return reader;
        }

        public int ExecuteRecordCount(string queryString)
        {
            int cantidad = 0;
            OdbcCommand comando = new OdbcCommand();
            try
            {
                
                //HttpContext.Current.Response.Write(queryString);
                if (_connector.State == ConnectionState.Closed)
                    OpenConnection();
                string[] separador = { "FROM" };
                string[] qery = queryString.Split(separador, StringSplitOptions.None);
                string qstr = " SELECT count(*) FROM " + qery[1];
                comando.Connection = _connector;
                comando.CommandText = qstr;
                cantidad = Convert.ToInt32(comando.ExecuteScalar());
                comando.Dispose();
            }
            catch (Exception e)
            {
                HttpContext.Current.Response.Write("Ocurrio un error en la aplicacion, intente nuevamente realizar la peticion.<br><br> el error fue el siguiente: <br>" + e.Message + "<br>aqui mero<br>" + queryString);
                //Registro del Error
                Error = true;
                ErrorManagement.LogError(e, HttpContext.Current, this,comando);
                //HttpContext.Current.Response.End();
            }
            finally
            {

            }

            return cantidad;
        }

        /// <summary>
        /// Realiza el cerrado de la conexion de datos ODBC, verifica si se genero algun error en toda la transaccion en cuyo caso realiza un Rollback, caso contrario realiza el Commit
        /// </summary>
        public void CloseConnection()
        {
            OdbcCommand comando = new OdbcCommand();
            if (DBType == 1 && !AutoCommit)
            {
                if (Error)
                {
                    comando.CommandText = "ROLLBACK;";
                    ErrorManagement.SendErrorLog();
                    //Enviar Email con registro de errores de DB
                }
                else
                    comando.CommandText = "COMMIT;";


                if (_connector.State == ConnectionState.Closed)
                    OpenConnection();
                comando.Connection = _connector;
                comando.ExecuteNonQuery();
            }
            _connector.Close();
            _connector.Dispose();
            //HttpContext.Current.Response.Write("]");
        }

    }
}
